
/* Result Sets Interface */
#ifndef SQL_CRSR
#  define SQL_CRSR
  struct sql_cursor
  {
    unsigned int curocn;
    void *ptr1;
    void *ptr2;
    unsigned int magic;
  };
  typedef struct sql_cursor sql_cursor;
  typedef struct sql_cursor SQL_CURSOR;
#endif /* SQL_CRSR */

/* Thread Safety */
typedef void * sql_context;
typedef void * SQL_CONTEXT;

/* Object support */
struct sqltvn
{
  unsigned char *tvnvsn; 
  unsigned short tvnvsnl; 
  unsigned char *tvnnm;
  unsigned short tvnnml; 
  unsigned char *tvnsnm;
  unsigned short tvnsnml;
};
typedef struct sqltvn sqltvn;

struct sqladts
{
  unsigned int adtvsn; 
  unsigned short adtmode; 
  unsigned short adtnum;  
  sqltvn adttvn[1];       
};
typedef struct sqladts sqladts;

static struct sqladts sqladt = {
  1,1,0,
};

/* Binding to PL/SQL Records */
struct sqltdss
{
  unsigned int tdsvsn; 
  unsigned short tdsnum; 
  unsigned char *tdsval[1]; 
};
typedef struct sqltdss sqltdss;
static struct sqltdss sqltds =
{
  1,
  0,
};

/* File name & Package Name */
struct sqlcxp
{
  unsigned short fillen;
           char  filnam[16];
};
static struct sqlcxp sqlfpn =
{
    15,
    "dm01_dynamic.pc"
};


static unsigned int sqlctx = 2215459;


static struct sqlexd {
   unsigned long  sqlvsn;
   unsigned int   arrsiz;
   unsigned int   iters;
   unsigned int   offset;
   unsigned short selerr;
   unsigned short sqlety;
   unsigned int   occurs;
            short *cud;
   unsigned char  *sqlest;
            char  *stmt;
   sqladts *sqladtp;
   sqltdss *sqltdsp;
   unsigned char  **sqphsv;
   unsigned long  *sqphsl;
            int   *sqphss;
            short **sqpind;
            int   *sqpins;
   unsigned long  *sqparm;
   unsigned long  **sqparc;
   unsigned short  *sqpadto;
   unsigned short  *sqptdso;
   unsigned int   sqlcmax;
   unsigned int   sqlcmin;
   unsigned int   sqlcincr;
   unsigned int   sqlctimeout;
   unsigned int   sqlcnowait;
            int   sqfoff;
   unsigned int   sqcmod;
   unsigned int   sqfmod;
   unsigned char  *sqhstv[4];
   unsigned long  sqhstl[4];
            int   sqhsts[4];
            short *sqindv[4];
            int   sqinds[4];
   unsigned long  sqharm[4];
   unsigned long  *sqharc[4];
   unsigned short  sqadto[4];
   unsigned short  sqtdso[4];
} sqlstm = {12,4};

/* SQLLIB Prototypes */
extern sqlcxt ( void **, unsigned int *,
                   struct sqlexd *, struct sqlcxp * );
extern sqlcx2t( void **, unsigned int *,
                   struct sqlexd *, struct sqlcxp * );
extern sqlbuft( void **, char * );
extern sqlgs2t( void **, char * );
extern sqlorat( void **, unsigned int *, void * );

/* Forms Interface */
static int IAPSUCC = 0;
static int IAPFAIL = 1403;
static int IAPFTL  = 535;
extern void sqliem( unsigned char *, signed int * );

typedef struct { unsigned short len; unsigned char arr[1]; } VARCHAR;
typedef struct { unsigned short len; unsigned char arr[1]; } varchar;

/* CUD (Compilation Unit Data) Array */
static short sqlcud0[] =
{12,4130,852,0,0,
5,0,0,1,0,0,32,29,0,0,0,0,0,1,0,
20,0,0,2,53,0,17,207,0,0,0,0,0,1,0,
35,0,0,2,0,0,45,213,0,0,1,1,0,1,0,1,3,0,0,
54,0,0,2,0,0,13,220,0,0,3,0,0,1,0,2,3,0,0,2,5,0,0,2,5,0,0,
81,0,0,2,0,0,15,224,0,0,0,0,0,1,0,
96,0,0,3,0,0,29,227,0,0,0,0,0,1,0,
111,0,0,4,0,0,30,241,0,0,0,0,0,1,0,
126,0,0,0,0,0,27,253,0,0,4,4,0,1,0,1,97,0,0,1,97,0,0,1,97,0,0,1,10,0,0,
};


#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "sqlca.h"
#include "oraca.h"
				
//演示dynamic-sql四种方法
//

extern sqlgls(char * , size_t *, size_t * );
extern sqlglmt(void *,char *,size_t *,size_t *);
void connet();

void sqlerr02();

void sqlerr02()
{
	char	stm[120];
	size_t	sqlfc, stmlen=120;
	unsigned int ret = 0;
	
	//出错时,可以把错误SQL语言给打印出来
	/* EXEC SQL WHENEVER SQLERROR CONTINUE; */ 

	
	ret = sqlgls(stm, &stmlen, &sqlfc);	
	printf("出错的SQL:%.*s\n", stmlen, stm);
	printf("出错原因:%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
	//printf("出错原因:%.70s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
	/* EXEC SQL ROLLBACK WORK RELEASE; */ 

{
 struct sqlexd sqlstm;
 sqlstm.sqlvsn = 12;
 sqlstm.arrsiz = 0;
 sqlstm.sqladtp = &sqladt;
 sqlstm.sqltdsp = &sqltds;
 sqlstm.iters = (unsigned int  )1;
 sqlstm.offset = (unsigned int  )5;
 sqlstm.cud = sqlcud0;
 sqlstm.sqlest = (unsigned char  *)&sqlca;
 sqlstm.sqlety = (unsigned short)4352;
 sqlstm.occurs = (unsigned int  )0;
 sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}


	exit(1);
}

void nodata()
{
	int ret = 0;
	printf("没有发现数据\n");
	if (sqlca.sqlcode != 0)
	{
		ret = sqlca.sqlcode;
		printf("sqlca.sqlcode: err:%d \n", sqlca.sqlcode);
		return ;
	}
}


typedef char dnameType[20];
typedef char locType[20];

/* EXEC SQL BEGIN DECLARE SECTION; */ 

	/* EXEC SQL TYPE dnameType is string(20); */ 

	/* EXEC SQL TYPE locType is string(20); */ 

	char 	*usrname = "scott";
	char 	*passwd = "tiger";
	char 	*serverid = "orcl";
	
	int				deptno;
	dnameType		dname; //string 数据类型  //varchar类型 和 char 类型的区别. 与编译选项有关系
	short 			dname_ind;
	locType 		loc;
	short 			loc_ind;	
	char 			mySql[1024];
	char 			*pSql;
/* EXEC SQL END DECLARE SECTION; */ 



//动态sql1
/*

//非select语言,无宿主变量
int main()
{
	int 	ret = 0;	
	int 	i = 0;
	char    choosechar;

	memset(mySql, 0, sizeof(mySql));
	pSql = NULL;
	
	EXEC SQL WHENEVER SQLERROR DO sqlerr02();
	connet();
	//EXEC SQL WHENEVER NOT FOUND DO nodata();
	
	pSql = mySql;
	//循环处理sql语言
	for(;;)
	{
		printf("\nplease enter sql(not select ): ");
		gets(mySql);
		//scanf("%s", mySql); --空格截断
		printf("mysql:%s\n", mySql);
		printf("任意键继续....\n");
		//getchar();
		EXEC SQL EXECUTE IMMEDIATE :pSql;
			
		EXEC SQL COMMIT;
		printf("继续执行吗?\n");
		scanf("%c", &choosechar);
		fflush(stdin);
		
		if (choosechar=='n' || choosechar=='N')
		{
			break;
		}
	}

	EXEC SQL COMMIT WORK RELEASE;
	printf("return ok...\n");

	return ret ;
}
insert into dept values(68, '68name', '68loc') 
*/

//非查询语言, 带固定数量的宿主变量
//使用内嵌PREPARE命令准备SQL语言
//动态sql2 有2步骤

/*
//1 PREPARE语法
EXEC SQL PREPARE statement_name FROM {:host_string | string_literal};
PREPARE是一个预编译器标识符,而不是宿主变量

//2 USING子句
EXEC SQL EXECUTE statement_name [USING :host_variable1[:indicator1] [, :host_variable2[:indicator2] ...  ] ;
*/


/*
int main()
{
	int 	ret = 0;	
	int 	i = 0;
	char    choosechar;

	memset(mySql, 0, sizeof(mySql));
	pSql = NULL;
	
	EXEC SQL WHENEVER SQLERROR DO sqlerr02();
	connet();
	//EXEC SQL WHENEVER NOT FOUND DO nodata();
	
	pSql = mySql;
	//循环处理sql语言
	for(;;)
	{
		printf("\n请输入要更新部门编号 ");
		scanf("%d", &deptno);
		
		printf("\n请输入要新loc值 ");
		scanf("%s", loc);
		
	
		//准备动态sql
		EXEC SQL PREPARE my_pre_sql FROM 'update dept set loc = :a where deptno = :b';
		//执行动态sql
		EXEC SQL EXECUTE my_pre_sql USING :loc, :deptno;
	
		EXEC SQL COMMIT;
		
		printf("\n 按任意键继续? ");
		getchar();
		printf("\n退出键入n, 其他继续? ");
		scanf("%c", &choosechar);
		fflush(stdin);
		
		if (choosechar=='n' || choosechar=='N')
		{
			break;
		}
	}

	EXEC SQL COMMIT WORK RELEASE;
	printf("return ok...\n");

	return ret ;
}
*/


//查询部门号大于10的所有部门信息
//动态sql3 处理选择列表项(select查询出来的结果列数固定) 和 输入宿主变量个数一定
//本质:	输入宿主变量个数固定 	查询条件固定  
//		输出宿主变量个数固定	返回结果固定

//可以结合游标一块使用

int main()
{
	int 	ret = 0;	
	int 	i = 0;
	char    choosechar;

	memset(mySql, 0, sizeof(mySql));
	pSql = NULL;
	
	/* EXEC SQL WHENEVER SQLERROR DO sqlerr02(); */ 

	connet();
	//EXEC SQL WHENEVER NOT FOUND DO nodata();

	//循环处理sql语言
	for(;;)
	{
		printf("\n请输入部门编号 ");
		scanf("%d", &deptno);
	
		//准备动态sql
		/* EXEC SQL PREPARE my_pre_sql3 FROM 'select deptno, dname, loc from dept where deptno > :a'; */ 

{
  struct sqlexd sqlstm;
  sqlstm.sqlvsn = 12;
  sqlstm.arrsiz = 0;
  sqlstm.sqladtp = &sqladt;
  sqlstm.sqltdsp = &sqltds;
  sqlstm.stmt = "select deptno, dname, loc from dept where deptno > :a";
  sqlstm.iters = (unsigned int  )1;
  sqlstm.offset = (unsigned int  )20;
  sqlstm.cud = sqlcud0;
  sqlstm.sqlest = (unsigned char  *)&sqlca;
  sqlstm.sqlety = (unsigned short)4352;
  sqlstm.occurs = (unsigned int  )0;
  sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
  if (sqlca.sqlcode < 0) sqlerr02();
}


		
		//定义游标
		/* EXEC SQL DECLARE c1 CURSOR FOR my_pre_sql3; */ 

		
		//打开游标
		/* EXEC SQL OPEN c1 USING :deptno; */ 

{
  struct sqlexd sqlstm;
  sqlstm.sqlvsn = 12;
  sqlstm.arrsiz = 1;
  sqlstm.sqladtp = &sqladt;
  sqlstm.sqltdsp = &sqltds;
  sqlstm.stmt = "";
  sqlstm.iters = (unsigned int  )1;
  sqlstm.offset = (unsigned int  )35;
  sqlstm.selerr = (unsigned short)1;
  sqlstm.cud = sqlcud0;
  sqlstm.sqlest = (unsigned char  *)&sqlca;
  sqlstm.sqlety = (unsigned short)4352;
  sqlstm.occurs = (unsigned int  )0;
  sqlstm.sqcmod = (unsigned int )0;
  sqlstm.sqhstv[0] = (unsigned char  *)&deptno;
  sqlstm.sqhstl[0] = (unsigned long )sizeof(int);
  sqlstm.sqhsts[0] = (         int  )0;
  sqlstm.sqindv[0] = (         short *)0;
  sqlstm.sqinds[0] = (         int  )0;
  sqlstm.sqharm[0] = (unsigned long )0;
  sqlstm.sqadto[0] = (unsigned short )0;
  sqlstm.sqtdso[0] = (unsigned short )0;
  sqlstm.sqphsv = sqlstm.sqhstv;
  sqlstm.sqphsl = sqlstm.sqhstl;
  sqlstm.sqphss = sqlstm.sqhsts;
  sqlstm.sqpind = sqlstm.sqindv;
  sqlstm.sqpins = sqlstm.sqinds;
  sqlstm.sqparm = sqlstm.sqharm;
  sqlstm.sqparc = sqlstm.sqharc;
  sqlstm.sqpadto = sqlstm.sqadto;
  sqlstm.sqptdso = sqlstm.sqtdso;
  sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
  if (sqlca.sqlcode < 0) sqlerr02();
}


			
		//提取数据
		/* EXEC SQL WHENEVER NOT FOUND DO break; */ 

		
		for (;;)
		{
			/* EXEC SQL FETCH c1 INTO :deptno, :dname,:loc:loc_ind; */ 

{
   struct sqlexd sqlstm;
   sqlstm.sqlvsn = 12;
   sqlstm.arrsiz = 3;
   sqlstm.sqladtp = &sqladt;
   sqlstm.sqltdsp = &sqltds;
   sqlstm.iters = (unsigned int  )1;
   sqlstm.offset = (unsigned int  )54;
   sqlstm.selerr = (unsigned short)1;
   sqlstm.cud = sqlcud0;
   sqlstm.sqlest = (unsigned char  *)&sqlca;
   sqlstm.sqlety = (unsigned short)4352;
   sqlstm.occurs = (unsigned int  )0;
   sqlstm.sqfoff = (         int )0;
   sqlstm.sqfmod = (unsigned int )2;
   sqlstm.sqhstv[0] = (unsigned char  *)&deptno;
   sqlstm.sqhstl[0] = (unsigned long )sizeof(int);
   sqlstm.sqhsts[0] = (         int  )0;
   sqlstm.sqindv[0] = (         short *)0;
   sqlstm.sqinds[0] = (         int  )0;
   sqlstm.sqharm[0] = (unsigned long )0;
   sqlstm.sqadto[0] = (unsigned short )0;
   sqlstm.sqtdso[0] = (unsigned short )0;
   sqlstm.sqhstv[1] = (unsigned char  *)dname;
   sqlstm.sqhstl[1] = (unsigned long )20;
   sqlstm.sqhsts[1] = (         int  )0;
   sqlstm.sqindv[1] = (         short *)0;
   sqlstm.sqinds[1] = (         int  )0;
   sqlstm.sqharm[1] = (unsigned long )0;
   sqlstm.sqadto[1] = (unsigned short )0;
   sqlstm.sqtdso[1] = (unsigned short )0;
   sqlstm.sqhstv[2] = (unsigned char  *)loc;
   sqlstm.sqhstl[2] = (unsigned long )20;
   sqlstm.sqhsts[2] = (         int  )0;
   sqlstm.sqindv[2] = (         short *)&loc_ind;
   sqlstm.sqinds[2] = (         int  )0;
   sqlstm.sqharm[2] = (unsigned long )0;
   sqlstm.sqadto[2] = (unsigned short )0;
   sqlstm.sqtdso[2] = (unsigned short )0;
   sqlstm.sqphsv = sqlstm.sqhstv;
   sqlstm.sqphsl = sqlstm.sqhstl;
   sqlstm.sqphss = sqlstm.sqhsts;
   sqlstm.sqpind = sqlstm.sqindv;
   sqlstm.sqpins = sqlstm.sqinds;
   sqlstm.sqparm = sqlstm.sqharm;
   sqlstm.sqparc = sqlstm.sqharc;
   sqlstm.sqpadto = sqlstm.sqadto;
   sqlstm.sqptdso = sqlstm.sqtdso;
   sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
   if (sqlca.sqlcode == 1403) break;
   if (sqlca.sqlcode < 0) sqlerr02();
}


			printf("%d\t %s\t %s \n", deptno, dname, loc); 
			
		}
		/* EXEC SQL CLOSE c1; */ 

{
  struct sqlexd sqlstm;
  sqlstm.sqlvsn = 12;
  sqlstm.arrsiz = 3;
  sqlstm.sqladtp = &sqladt;
  sqlstm.sqltdsp = &sqltds;
  sqlstm.iters = (unsigned int  )1;
  sqlstm.offset = (unsigned int  )81;
  sqlstm.cud = sqlcud0;
  sqlstm.sqlest = (unsigned char  *)&sqlca;
  sqlstm.sqlety = (unsigned short)4352;
  sqlstm.occurs = (unsigned int  )0;
  sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
  if (sqlca.sqlcode < 0) sqlerr02();
}


		
	
		/* EXEC SQL COMMIT; */ 

{
  struct sqlexd sqlstm;
  sqlstm.sqlvsn = 12;
  sqlstm.arrsiz = 3;
  sqlstm.sqladtp = &sqladt;
  sqlstm.sqltdsp = &sqltds;
  sqlstm.iters = (unsigned int  )1;
  sqlstm.offset = (unsigned int  )96;
  sqlstm.cud = sqlcud0;
  sqlstm.sqlest = (unsigned char  *)&sqlca;
  sqlstm.sqlety = (unsigned short)4352;
  sqlstm.occurs = (unsigned int  )0;
  sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
  if (sqlca.sqlcode < 0) sqlerr02();
}


		
		printf("\n 按任意键继续? ");
		getchar();
		printf("\n键入 n 退出, 其他继续? ");
		scanf("%c", &choosechar);
		fflush(stdin);
		
		if (choosechar=='n' || choosechar=='N')
		{
			break;
		}
	}

	/* EXEC SQL COMMIT WORK RELEASE; */ 

{
 struct sqlexd sqlstm;
 sqlstm.sqlvsn = 12;
 sqlstm.arrsiz = 3;
 sqlstm.sqladtp = &sqladt;
 sqlstm.sqltdsp = &sqltds;
 sqlstm.iters = (unsigned int  )1;
 sqlstm.offset = (unsigned int  )111;
 sqlstm.cud = sqlcud0;
 sqlstm.sqlest = (unsigned char  *)&sqlca;
 sqlstm.sqlety = (unsigned short)4352;
 sqlstm.occurs = (unsigned int  )0;
 sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
 if (sqlca.sqlcode < 0) sqlerr02();
}


	printf("return ok...\n");

	return ret ;
}		



void connet()
{
	int ret = 0;
	//连接数据库
	/* EXEC SQL CONNECT:usrname IDENTIFIED BY:passwd USING:serverid ; */ 

{
 struct sqlexd sqlstm;
 sqlstm.sqlvsn = 12;
 sqlstm.arrsiz = 4;
 sqlstm.sqladtp = &sqladt;
 sqlstm.sqltdsp = &sqltds;
 sqlstm.iters = (unsigned int  )10;
 sqlstm.offset = (unsigned int  )126;
 sqlstm.cud = sqlcud0;
 sqlstm.sqlest = (unsigned char  *)&sqlca;
 sqlstm.sqlety = (unsigned short)4352;
 sqlstm.occurs = (unsigned int  )0;
 sqlstm.sqhstv[0] = (unsigned char  *)usrname;
 sqlstm.sqhstl[0] = (unsigned long )0;
 sqlstm.sqhsts[0] = (         int  )0;
 sqlstm.sqindv[0] = (         short *)0;
 sqlstm.sqinds[0] = (         int  )0;
 sqlstm.sqharm[0] = (unsigned long )0;
 sqlstm.sqadto[0] = (unsigned short )0;
 sqlstm.sqtdso[0] = (unsigned short )0;
 sqlstm.sqhstv[1] = (unsigned char  *)passwd;
 sqlstm.sqhstl[1] = (unsigned long )0;
 sqlstm.sqhsts[1] = (         int  )0;
 sqlstm.sqindv[1] = (         short *)0;
 sqlstm.sqinds[1] = (         int  )0;
 sqlstm.sqharm[1] = (unsigned long )0;
 sqlstm.sqadto[1] = (unsigned short )0;
 sqlstm.sqtdso[1] = (unsigned short )0;
 sqlstm.sqhstv[2] = (unsigned char  *)serverid;
 sqlstm.sqhstl[2] = (unsigned long )0;
 sqlstm.sqhsts[2] = (         int  )0;
 sqlstm.sqindv[2] = (         short *)0;
 sqlstm.sqinds[2] = (         int  )0;
 sqlstm.sqharm[2] = (unsigned long )0;
 sqlstm.sqadto[2] = (unsigned short )0;
 sqlstm.sqtdso[2] = (unsigned short )0;
 sqlstm.sqphsv = sqlstm.sqhstv;
 sqlstm.sqphsl = sqlstm.sqhstl;
 sqlstm.sqphss = sqlstm.sqhsts;
 sqlstm.sqpind = sqlstm.sqindv;
 sqlstm.sqpins = sqlstm.sqinds;
 sqlstm.sqparm = sqlstm.sqharm;
 sqlstm.sqparc = sqlstm.sqharc;
 sqlstm.sqpadto = sqlstm.sqadto;
 sqlstm.sqptdso = sqlstm.sqtdso;
 sqlstm.sqlcmax = (unsigned int )100;
 sqlstm.sqlcmin = (unsigned int )2;
 sqlstm.sqlcincr = (unsigned int )1;
 sqlstm.sqlctimeout = (unsigned int )0;
 sqlstm.sqlcnowait = (unsigned int )0;
 sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
 if (sqlca.sqlcode < 0) sqlerr02();
}


	if (sqlca.sqlcode != 0)
	{
		ret = sqlca.sqlcode;
		printf("sqlca.sqlcode: err:%d \n", sqlca.sqlcode);
		return ;
	}
	else
	{
		printf("connect ok...\n");
	}
}
    




